package ar.edu.isft189.dao;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.ArrayList;
import java.util.List;

import sun.org.mozilla.javascript.internal.json.JsonParser.ParseException;
import ar.edu.isft189.model.AlumnoExamen;
import ar.edu.isft189.model.MesaExamen;
import ar.edu.isft189.utility.DBUtility;
import ar.edu.isft189.utility.Resultado;
import ar.edu.isft189.utility.UtilsTools;

public class MesaExamenCrudDao {
	private Connection connection;
	Resultado result;

	public MesaExamenCrudDao() {
		connection = DBUtility.getConnection();
		result = new Resultado();
	}

	/**
	 * Actualiza una mesa examen
	 * 
	 * @param me
	 * @throws SQLException
	 */
	public void updateMesaExamen(MesaExamen me) throws SQLException {
		try {
			PreparedStatement preparedStatement = connection
					.prepareStatement("UPDATE TBL_MESAEX SET ESTADO = ?, LIBRO = ? , "
							+ " FOLIO = ? , CANTEXAM = ?, CANTAUS = ?, CANTAPR = ? "
							+ " WHERE IDM = ?");

			// Parameters start with 1
			preparedStatement.setString(1, me.getEstado());
			preparedStatement.setInt(2, me.getLibro());
			preparedStatement.setInt(3, me.getFolio());
			preparedStatement.setInt(4, me.getCantExam());
			preparedStatement.setInt(5, me.getCantAus());
			preparedStatement.setInt(6, me.getCantAprob());
			preparedStatement.setInt(7, me.getIdm());

			preparedStatement.executeUpdate();

		} catch (SQLException sqle) {
			sqle.printStackTrace();
		} catch (Exception ex) {
			ex.printStackTrace();
		}
	}

	/**
	 * Retorna todas las mesas de examenes abiertas si el que se loguea es un
	 * docente se le pasa por parametro el numero de legajo sino trae todas las
	 * mesas de examenes abiertas
	 * 
	 * @param String
	 *            lt legajo-Titular
	 * @return List<MesaExamen>
	 */
	public List<MesaExamen> getAllMesas(String lt) {
		List<MesaExamen> mesasList = new ArrayList<MesaExamen>();
		try {
			Statement statement = connection.createStatement();

			// Armado de query

			String query = " SELECT a.IDM, a.FECHA, a.LIBRO, a.FOLIO, a.CODIGOM, tm.DESCR as materia"
					+ " ,a.TITULAR, td.APENOM as apenomTitular,a.VOCAL1,td1.APENOM as apenomV1,a.VOCAL2,"
					+ " td2.APENOM as apenomV2,a.CANTEXAM, a.CANTAUS,a.CANTAPR, a.ESTADO, tp.DESCR as carrera  FROM TBL_MESAEX a"
					+ " INNER JOIN TBL_MATERIA tm ON a.CODIGOM = tm.CODIGOM INNER JOIN TBL_DOCENTE td ON a.TITULAR = td.LEGAJO"
					+ "	INNER JOIN TBL_DOCENTE td1 ON a.VOCAL1 = td1.LEGAJO INNER JOIN TBL_DOCENTE td2  ON a.VOCAL2 = td2.LEGAJO"
					+ " INNER JOIN TBL_PLANE tp ON tm.CODIGO = tp.CODIGO"
					+ " WHERE (a.ESTADO = 'A' OR a.ESTADO = 'P') ";

			if (lt != null) {
				query += " AND a.TITULAR = " + lt;
			}

			query += " ORDER BY a.IDM ASC";

			ResultSet rs = statement.executeQuery(query);

			while (rs.next()) {
				MesaExamen me = new MesaExamen();

				me.setIdm(rs.getInt("idm"));
				me.setFecha(rs.getString("fecha"));
				me.setLibro(rs.getObject("libro") != null ? rs.getInt("libro")
						: 0);
				me.setFolio(rs.getObject("folio") != null ? rs.getInt("folio")
						: 0);
				me.setCodigoM(rs.getInt("codigoM"));
				me.setMateria(rs.getString("materia"));
				me.setTitular(rs.getInt("titular"));
				me.setApeNomTitular(rs.getString("apenomTitular"));
				me.setVocal1(rs.getInt("vocal1"));
				me.setApeNomV1(rs.getString("apenomV1"));
				me.setVocal2(rs.getInt("vocal2"));
				me.setApeNomV2(rs.getString("apenomV2"));
				me.setCantExam(rs.getObject("cantExam") != null ? rs
						.getInt("cantExam") : 0);
				me.setCantAus(rs.getObject("cantAus") != null ? rs
						.getInt("cantAus") : 0);
				me.setCantAprob(rs.getObject("cantApr") != null ? rs
						.getInt("cantApr") : 0);
				me.setEstado(rs.getString("estado"));
				me.setCarrera(rs.getString("carrera"));

				mesasList.add(me);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (Exception e) {
			System.out.println("Error en getStudentsByIdm " + e.toString());
			e.printStackTrace();
		}

		return mesasList;
	}

	/**
	 * Metodo que retorna la lista de alumnos para una mesa de examen
	 * determinada
	 * 
	 * @param mesaId
	 * @return List<AlumnoExamen>
	 */
	public List<AlumnoExamen> getStudentsByIdm(int mesaId) {

		List<AlumnoExamen> lstAlu = new ArrayList<AlumnoExamen>();

		try {
			PreparedStatement preparedStatement = connection
					.prepareStatement("SELECT a.IDM, a.LEGAJOA, ta.APENOM, tp.DESCR as carrera, a.FECHA_INS, a.CODIGOME, "
							+ " tm.DESCR as materia, a.NOTA, ta.NRODOC FROM TBL_MESAEX_AL a INNER JOIN TBL_ALUMNO ta ON "
							+ " a.LEGAJOA = ta.LEGAJOA INNER JOIN TBL_PLANE tp  ON tp.CODIGO = ta.IDPLAN "
							+ " INNER JOIN TBL_MATERIA tm ON tm.CODIGOM = a.CODIGOME WHERE a.IDM = ?");

			preparedStatement.setInt(1, mesaId);
			ResultSet rs = preparedStatement.executeQuery();

			if (rs.next()) {

				AlumnoExamen al = new AlumnoExamen();

				al.setIdm(rs.getInt("idm"));
				al.setLegajoA(rs.getInt("legajoA"));
				al.setApenom(rs.getString("apenom"));
				al.setFecha_ins(rs.getString("fecha_ins"));
				al.setCodigoMe(rs.getObject("codigoMe") != null ? rs
						.getInt("codigoMe") : 0);
				al.setCarrera(rs.getString("carrera"));
				al.setMateria(rs.getString("materia"));
				al.setNota(rs.getObject("nota") != null ? rs.getInt("nota") : 0);

				lstAlu.add(al);
			}
		} catch (SQLException sqle) {
			System.out.println("Error en getStudentsByIdm " + sqle.toString());
			sqle.printStackTrace();
		} catch (Exception e) {
			System.out.println("Error en getStudentsByIdm " + e.toString());
			e.printStackTrace();
		}

		return lstAlu;
	}

	/**
	 * Metodo que actualiza la nota de un alumno deteminado
	 * 
	 * @param user
	 * @throws ParseException
	 */
	public void updateNotaAlumno(AlumnoExamen ae) throws SQLException {
		try {
			PreparedStatement preparedStatement = connection
					.prepareStatement("UPDATE TBL_MESAEX_AL SET NOTA= ?"
							+ " WHERE IDM= ? AND CODIGOME= ? AND LEGAJOA= ?");
			// Parameters start with 1
			preparedStatement.setInt(1, ae.getNota());
			preparedStatement.setInt(2, ae.getIdm());
			preparedStatement.setInt(3, ae.getCodigoMe());
			preparedStatement.setInt(4, ae.getLegajoA());
			preparedStatement.executeUpdate();

			connection.commit();

		} catch (SQLException sqle) {
			System.out.println("Error en updateNotaAlumno " + sqle.toString());
			sqle.printStackTrace();
		} catch (Exception ex) {
			System.out.println("Error en updateNotaAlumno " + ex.toString());
			ex.printStackTrace();
		}
	}

	/**
	 * Metodo que cierra una mesa de examen
	 * 
	 * @param idm
	 * @throws SQLException
	 */
	public void cerrarMesaExamen(int idm) throws SQLException {
		try {
			PreparedStatement preparedStatement = connection
					.prepareStatement("UPDATE TBL_MESAEX SET ESTADO= 'C'"
							+ " WHERE IDM= ? ");
			// Parameters start with 1
			preparedStatement.setInt(1, idm);
			preparedStatement.executeUpdate();

			connection.commit();

		} catch (SQLException sqle) {
			System.out.println("Error en cerrarMesaExamen " + sqle.toString());
			sqle.printStackTrace();
		} catch (Exception ex) {
			System.out.println("Error en cerrarMesaExamen " + ex.toString());
			ex.printStackTrace();
		}
	}

	/**
	 * Crea una mesa de examen
	 * 
	 * @param me
	 * @return
	 * @throws SQLException
	 */
	public MesaExamen crearMesaExamen(MesaExamen me) throws SQLException {

		try {

			GeneralCrudDao gralDao = new GeneralCrudDao();
			me.setIdm(gralDao.getMaxIdm());

			CallableStatement cs = connection
					.prepareCall("{call P_ABM_TBL_MESAEX(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}");

			cs.setString(1, "A");
			cs.setInt(2, me.getIdm());
			cs.setDate(3, UtilsTools.convertStrToDateSql(me.getFecha()));
			cs.setInt(4, me.getLibro());
			cs.setInt(5, me.getFolio());
			cs.setString(6, "A");
			cs.setInt(7, me.getCodigoM());
			cs.setInt(8, me.getTitular());
			cs.setInt(9, me.getVocal1());
			cs.setInt(10, me.getVocal2());
			cs.setInt(11, me.getCantExam());
			cs.setInt(12, me.getCantAus());
			cs.setInt(13, me.getCantAprob());
			cs.registerOutParameter(14, Types.INTEGER);
			cs.registerOutParameter(15, Types.VARCHAR);

			cs.executeUpdate();

			// commit de la accion
			connection.commit();

		} catch (SQLException sqle) {
			System.out.println("Error en crearMesaExamen " + sqle.toString());
			sqle.printStackTrace();
		} catch (Exception ex) {
			System.out.println("Error en crearMesaExamen " + ex.toString());
			ex.printStackTrace();
		}

		return me;
	}

	/**
	 * Abre una mesa de examen que esta en estado P
	 * 
	 * @param idm
	 */
	public void abrirMesaExamen(int idm) {

		try {

			PreparedStatement ps = connection
					.prepareStatement("UPDATE TBL_MESAEX SET ESTADO = 'A' WHERE IDM = ?");

			ps.setInt(1, idm);
			ps.executeUpdate();

			// commit de la accion
			connection.commit();

			return;

		} catch (SQLException sqle) {
			sqle.printStackTrace();
		} catch (Exception ex) {
			ex.printStackTrace();
		}
	}
}
